<?php

/**
 * 数据库类
 */
class Database {

    /**
     * 单例模式数据
     * @var array 
     */
    protected static $_instance = array();

    /**
     * 当前数据库pdo对象
     * @var PDO object 
     */
    protected $pdo;

    /**
     * PDOstatement对象集合
     * @var PDOstatement object 
     */
    protected $result = array();

    /**
     * 数据库配置信息
     * @var array 
     */
    protected $info = array('driver' => 'mysql', 'host' => 'localhost', 'port' => 3306);

    /**
     * SQL语句查询数组
     * @var array 
     */
    protected $statement = array();

    /**
     * 数据库表集合
     * @var array 
     */
    protected $table = array();

    /**
     * 所有表主键集合
     * @var array 
     */
    protected $key = array();

    /**
     * 所有SQL语句查询结果
     * @var array 
     */
    public $sql = array();

    /**
     * 构造函数
     * @param array $config 数据库配置信息
     */
    public function __construct($config = array()) {
        $this->info = array_merge($this->info, $config);
    }

    /**
     * 连接数据库
     */
    public function connect() {
        try {
            $this->pdo = new pdo($this->info['driver'] . ':host=' . $this->info['host'] . ';port=' . $this->info['port'] . ';dbname=' . $this->info['dbname'], $this->info['user'], $this->info['password'], array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
            ));
        } catch (PDOException $e) {
            Logger::getInstance()->error($e, TRUE);
            exit;
        }
        unset($this->info['password']);
    }

    /**
     * 判断数据库状态,断开重连
     * @return boolean
     */
    public function ping() {
        try {
            $this->getPDO()->query('SELECT 1');
        } catch (PDOException $e) {
            $this->connect();
        }
        return TRUE;
    }

    /**
     * 单例模式
     * @param string $name 数据库标识
     * @param array $config 数据库配置
     * @return Database
     */
    public static function getInstance($name = 'default', $config = array()) {
        if (isset(static::$_instance[$name]))
            return static::$_instance[$name];
        return static::$_instance[$name] = new static($config);
    }

    /**
     * 判断传入的数据是否为标量
     * @param mixed $data
     * @return boolean
     */
    protected static function _is_plain($data) {
        if (!is_scalar($data))
            return false;
        return is_string($data) ? !preg_match('/\W/i', $data) : true;
    }

    /**
     * 移除注释语句
     * @param string $sql
     * @return string
     */
    protected function uncomment($sql) {
        /* '@
          (([\'"`]).*?[^\\\]\2) # $1 : Skip single & double quoted expressions
          |(                    # $3 : Match comments
          (?:\#|--).*?$       # - Single line comments
          |                   # - Multi line (nested) comments
          /\*                 #   . comment open marker
          (?: [^/*]         #   . non comment-marker characters
          |/(?!\*)        #   . ! not a comment open
          |\*(?!/)        #   . ! not a comment close
          |(?R)           #   . recursive case
          )*                #   . repeat eventually
          \*\/                #   . comment close marker
          )\s*                  # Trim after comments
          |(?<=;)\s+            # Trim after semi-colon
          @msx' */
        $sql = str_replace("{pre}", $this->info['prefix'], $sql);
        return trim(preg_replace('@(([\'"`]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms', '$1', $sql));
    }

    /**
     * 构造查询的参数字符串
     * @param mixed $data 原始数据
     * @param string $operator 操作符,默认为 =
     * @param string $glue 连接符,默认为,
     * @param string $mode 替换模式,默认为:
     * @return string
     */
    protected static function _params(& $data, $operator = '=', $glue = ', ', $mode = ':') {
        $params = is_string($data) ? array($data) : array_keys((array) $data);
        foreach ($params as &$param) {
            if (isset($data[$param]) && stripos($data[$param], ':' . $param) !== FALSE) {
                $paramdata = $data[$param];
                unset($data[$param]);
                $param = implode(' ', array(self::_escape($param), $operator, '`' . $param . '`' . str_replace(':' . $param, '', $paramdata)));
            } else {
                $param = implode(' ', array(self::_escape($param), $operator, ($mode == ':' ? ':' . $param : '?')));
            }
        }
        return implode($glue, $params);
    }

    /**
     * 给字段加上标识符
     * @param string $field
     * @return mixed
     */
    protected static function _escape($field) {
        return self::_is_plain($field) ? '`' . $field . '`' : $field;
    }

    /**
     * 处理字段别名数据
     * @param array $alias
     * @return array
     */
    protected static function _alias(array $alias) {
        foreach ($alias as $k => $v) {
            $_alias[] = self::_escape($v) . ( is_string($k) ? ' AS ' . self::_escape($k) : '' );
        }
        return $_alias;
    }

    /**
     * 构造查询字段字符串
     * @param mixed $fields
     * @return string
     */
    protected static function _fields($fields) {
        if (empty($fields))
            return '*';
        if (is_string($fields))
            return $fields;
        return implode(', ', self::_alias($fields));
    }

    /**
     * 构造查询的条件和参数
     * @param array $conditions
     * @return object
     */
    protected static function _conditions(array $conditions, $operator = '=', $glue = ', ', $mode = ':') {
        $sql = array();
        $params = array();
        $i = 0;
        foreach ($conditions as $condition => $param) {
            if (is_string($condition)) {
                for ($keys = array(), $n = 0; false !== ( $n = strpos($condition, '?', $n) ); $n ++) {
                    $condition = substr_replace($condition, ':' . ( $keys[] = '_' . ++$i ), $n, 1);
                }
                if (!empty($keys))
                    $param = array_combine($keys, (array) $param);
                if (self::_is_plain($condition)) {
                    $param = array($condition => (string) $param);
                    $condition = self::_params($condition, $operator, $glue, $mode);
                }
                $params += (array) $param;
            } else {
                $condition = $param;
            }
            $sql[] = $condition;
        }
        return (object) array(
                    'sql' => '( ' . implode(' ) AND ( ', $sql) . ' )',
                    'params' => $params
        );
    }

    /**
     * 替换SQL参数,获得完整SQL语句
     * @param string $sql
     * @param array $params
     * @return string
     */
    protected function _replace($sql, $params) {
        $indexed = $params == array_values($params);
        foreach ($params as $k => $v) {
            if (is_string($v))
                $v = "'$v'";
            if ($indexed)
                $sql = preg_replace('/\?/', $v, $sql, 1);
            else
                $sql = str_replace(":$k", $v, $sql);
        }
        return $sql;
    }

    /**
     * 取出二维数组中指定列的值
     * @param array $data
     * @param string $field
     * @return mixed
     */
    protected static function _column(array $data, $field) {
        $column = array();
        foreach ($data as $key => $row) {
            if (is_object($row) && isset($row->{$field})) {
                $column[$key] = $row->{$field};
            } else if (is_array($row) && isset($row[$field])) {
                $column[$key] = $row[$field];
            } else {
                $column[$key] = null;
            }
        }
        return $column;
    }

    /**
     * 将数据元素索引换成指定字段的值
     * @param array $data
     * @param string $field
     * @return array
     */
    protected static function _index(array $data, $field) {
        return array_combine(
                self::_column($data, $field), $data
        );
    }

    /**
     * 记录调试日志
     * 
     * @param string $id
     * @param string $type
     * @param array $params
     */
    protected function _logger($id = 'default', $type = 'start', $params = array()) {
        if ($this->info['debug']) {
            $loggerid = $id . '-db';
            switch ($type) {
                case 'start':
                    Logger::getInstance()->start($loggerid, FALSE);
                    break;
                default:
                    $info = Logger::getInstance()->stop($loggerid, FALSE);
                    array_push($this->sql, array(
                        'sql' => $this->_replace($this->getQueryString($id), $params), 'id' => $this->id(), 'count' => $this->count(),
                        'microtime' => $info['microtime'], 'memusage' => $info['memusage'], 'microtimeused' => $info['microtimeused']
                            )
                    );
                    break;
            }
        }
    }

    /**
     * 获取查询语句
     * @param string $id
     * @return mixed
     */
    public function getQueryString($id = 'default') {
        return isset($this->result[$id]) && $this->result[$id] ? $this->result[$id]->queryString : null;
    }

    /**
     * 获取当前的PDO对象
     * @return PDO
     */
    public function getPDO() {
        if (is_null($this->pdo)) {
            $this->connect();
        }
        return $this->pdo;
    }

    /*
     * 开始事务
     */

    public function beginTransaction() {
        $this->getPDO()->beginTransaction();
    }

    /**
     * 回滚
     */
    public function rollback() {
        $this->getPDO()->rollback();
    }

    /**
     * 提交
     */
    public function commit() {
        $this->getPDO()->commit();
    }

    /**
     * 执行一条SQL语句,不带参数,直接查询
     * @param string $sql 查询语句
     * @param string $id 标识
     * @return Database
     */
    public function raw($sql, $id = 'default') {
        $this->_logger($id, 'start');
        $this->result[$id] = $this->getPDO()->query($this->uncomment($sql));
        $this->_logger($id, 'stop');
        return $this;
    }

    /**
     * 执行一条SQL语句,带参数,预编译查询
     * @param string $sql 查询语句
     * @param array $params 参数
     * @param string $id 标识
     * @return Database
     */
    public function query($sql, $params = array(), $id = 'default') {
        $this->_logger($id, 'start');
        //获取预处理对象
        $this->result[$id] = isset($this->statement[$sql]) ?
                $this->statement[$sql] :
                $this->statement[$sql] = $this->getPDO()->prepare($this->uncomment($sql));
        $params = $params ? : array();
        //根据参数执行一条预处理语句
        $this->result[$id]->execute($params);
        if ($this->info['debug']) {
            $this->_logger($id, 'stop', $params);
        }
        return $this;
    }

    /**
     * 匹配查询的下一条记录
     * @param string $id 查询标识
     * @param int $type 
     * @return mixed
     */
    public function fetch($id = 'default', $type = PDO::FETCH_ASSOC) {
        if (!isset($this->result[$id]) || !$this->result[$id])
            throw new Exception('Can\'t fetch result if no query!');
        return $this->result[$id]->fetch($type);
    }

    /**
     * 匹配出查询的所有记录
     * @param string $id 查询标识
     * @param int $type 
     * @return mixed
     */
    public function fetchAll($id = 'default', $type = PDO::FETCH_ASSOC) {
        if (!isset($this->result[$id]) || !$this->result[$id])
            throw new Exception('Can\'t fetch results if no query!');
        return $this->result[$id]->fetchAll($type);
    }

    /**
     * 匹配出下一行指定列的数据结果
     * @param int $index 列索引,默认为0指第1列
     * @param string $id 标识
     * @return mixed 失败时返回false
     */
    public function fetchColumn($index = 0, $id = 'default') {
        if (!isset($this->result[$id]) || !$this->result[$id])
            throw new Exception('Can\'t fetch results if no query!');
        return $this->result[$id]->fetchColumn($index);
    }

    /**
     * 返回一条查询结果
     * @param string $sql SQL语句
     * @param array $params 传入的参数
     * @param string $id 标识,默认为default
     * @param int $type 返回类型,默认为数组
     * @return array 一维数组
     */
    public function one($sql, $params = array(), $id = 'one', $type = PDO::FETCH_ASSOC) {
        if (stripos(strtolower($sql), "limit") === false)
            $sql = preg_replace("/([;])/", '', trim($sql)) . " LIMIT 0,1";
        return $this->query($sql, $params, $id)->fetch($id, $type);
    }

    /**
     * 返回全部查询结果
     * @param string $sql SQL语句
     * @param array $params 传入的参数
     * @param string $id 标识,默认为default
     * @param int $type 返回类型,默认为数组
     * @return array 二维数组
     */
    public function all($sql, $params = array(), $id = 'all', $type = PDO::FETCH_ASSOC) {
        return $this->query($sql, $params, $id)->fetchAll($id, $type);
    }

    /**
     * 读取字段列数据
     * @param string $field 字段名
     * @param mixed $index 使用该列值来重置数组下标
     * @param string $id 标识,默认为default
     * @return array
     */
    public function column($field, $index = null, $id = 'default') {
        $data = $this->fetchAll($id);
        $values = self::_column($data, $field);
        return is_string($index) ?
                array_combine(self::_column($data, $index), $values) :
                $values;
    }

    /**
     * 获取表名的主键
     * @param string $table 表名
     * @return string
     */
    public function key($table) {
        $table = $this->_uncomment($table);
        if (self::config($table . ':PK'))
            return self::config($table . ':PK');
        else if (isset($this->key[$table]))
            return $this->key[$table];
        $keys = array_keys(self::_column($this->fields($table), 'key'), 'PRI');
        if (empty($keys))
            throw new Exception('No primary key on ' . $table . ' table, please set a primary key');
        return $this->key[$table] = $keys;
    }

    /**
     * 读取表的所有字段数据
     * @param string $table 表名
     * @return array
     */
    public function fields($table) {
        if (isset($this->table[$table]))
            return $this->table[$table];
        $sql = 'SELECT 
                            `COLUMN_NAME`                                               AS `name`, 
                            `COLUMN_DEFAULT`                                            AS `default`, 
                            NULLIF( `IS_NULLABLE`, "NO" )                               AS `null`, 
                            `DATA_TYPE`                                                 AS `type`, 
                            COALESCE( `CHARACTER_MAXIMUM_LENGTH`, `NUMERIC_PRECISION` ) AS `length`, 
                            `CHARACTER_SET_NAME`                                        AS `encoding`, 
                            `COLUMN_KEY`                                                AS `key`, 
                            `EXTRA`                                                     AS `auto`, 
                            `COLUMN_COMMENT`                                            AS `comment`
                    FROM `INFORMATION_SCHEMA`.`COLUMNS`
                    WHERE 
                            `TABLE_SCHEMA` = ' . $this->quote($this->info['dbname']) . ' AND 
                            `TABLE_NAME` = ' . $this->quote($table) . '
                    ORDER BY `ORDINAL_POSITION` ASC';
        $fields = $this->raw($sql);
        if (!$fields->count())
            throw new Exception('No ' . $table . ' table, please specify a valid table');
        return $this->table[$table] = self::_index($fields->fetchAll(), 'name');
    }

    public function quote($value) {
        return is_null($value) ? 'NULL' : $this->getPDO()->quote($value);
    }

    /**
     * 获取最后插入的ID
     * @return string
     */
    public function id() {
        return $this->getPDO()->lastInsertId();
    }

    /**
     * 统计最后影响的记录数
     * @return mixed
     */
    public function count($id = 'default') {
        return isset($this->result[$id]) && $this->result[$id] ? $this->result[$id]->rowCount() : null;
    }

    /* CRUD 操作方法 */

    public function insert($table, $data, $replace = FALSE, $delayed = FALSE) {
        return $this->create($table, $data, $replace, $delayed);
    }

    public function select($table, $where) {
        return $this->read($table, $where);
    }

    /**
     * 插入表记录
     * @param string $table 表名
     * @param array $data 插入的数据
     * @return Database
     */
    public function create($table, $data, $replace = FALSE, $delayed = FALSE) {
        $keys = array_keys($data);
        $insert = $replace ? 'REPLACE' : 'INSERT';
        $insert = $delayed ? $insert . ' DELAYED' : $insert;
        $sql = $insert . ' INTO ' . $table . ' (`' . implode('`, `', $keys) . '`) VALUES (:' . implode(', :', $keys) . ')';
        return $this->query($sql, $data);
    }

    /**
     * 读取表记录
     * @param string $table 表名
     * @param mixed $where 筛选条件 数组或字符串
     * @return Database
     */
    public function read($table, $where) {
        $sql = 'SELECT * FROM ' . $table . ' WHERE ' . (is_array($where) ? self::_conditions($where, '=', ',', ':')->sql : $where);
        return $this->query($sql, is_array($where) ? $where : array());
    }

    /**
     * 更新表记录
     * @param string $table 表名
     * @param array $data 更新的表数据, 可使用array('id'=>3, 'count'=>':count + 1')形式
     * @param mixed $where 筛选条件 数组或字符串
     * @return Database
     */
    public function update($table, $data, $where) {
        $sql = 'UPDATE ' . $table . ' SET ' . self::_params($data, '=', ',', '?') . ' WHERE ' . (is_array($where) ? self::_conditions($where, '=', ',', '?')->sql : $where);
        $values = array_merge(array_values($data), is_array($where) ? array_values($where) : array());
        return $this->query($sql, $values);
    }

    /**
     * 删除表记录
     * @param string $table 表名
     * @param mixed $where 筛选条件 数组或字符串
     * @return Database
     */
    public function delete($table, $where) {
        $sql = 'DELETE FROM ' . $table . ' WHERE ' . (is_array($where) ? self::_conditions($where, '=', ',', ':')->sql : $where);
        return $this->query($sql, is_array($where) ? $where : array());
    }

}
